import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import pandas as pd
import xlwings as xw
import os
from datetime import datetime
from pathlib import Path

class DataProcessingApp:
    def __init__(self, root):
        self.root = root
        self.root.title("电厂数据处理系统")        
        # 设置窗口大小和位置
        window_width = 1000
        window_height = 800
        screen_width = root.winfo_screenwidth()
        screen_height = root.winfo_screenheight()
        x = (screen_width - window_width) // 2
        y = (screen_height - window_height) // 2
        self.root.geometry(f"{window_width}x{window_height}+{x}+{y}")

        # 设置样式
        self.setup_styles()
        
        # 创建主框架
        self.main_frame = ttk.Frame(root, padding="10")
        self.main_frame.pack(fill=tk.BOTH, expand=True)

        self.create_widgets()

    def setup_styles(self):
        style = ttk.Style()
        style.configure('TButton', padding=5, font=('微软雅黑', 10))
        style.configure('TLabel', padding=5, font=('微软雅黑', 10))
        style.configure('Header.TLabel', font=('微软雅黑', 12, 'bold'))
        style.configure('TLabelframe', font=('微软雅黑', 10))
        style.configure('TLabelframe.Label', font=('微软雅黑', 10, 'bold'))

    def create_widgets(self):
        # 标题
        header = ttk.Label(self.main_frame, text="侯马热电快报及生产月报导入系统", style='Header.TLabel')
        header.pack(pady=10)

        # 文件选择区域
        file_frame = ttk.LabelFrame(self.main_frame, text="文件选择", padding="10")
        file_frame.pack(fill=tk.X, padx=5, pady=5)

        # 财务快报文件夹选择
        ttk.Label(file_frame, text="财务快报文件夹:").grid(row=0, column=0, sticky="w")
        self.finance_path_var = tk.StringVar()
        ttk.Entry(file_frame, textvariable=self.finance_path_var, width=80).grid(row=0, column=1, padx=5)
        ttk.Button(file_frame, text="选择", command=lambda: self.select_directory("finance")).grid(row=0, column=2, padx=5)

        # 生产月报文件选择
        ttk.Label(file_frame, text="生产月报文件:").grid(row=1, column=0, sticky="w", pady=5)
        self.production_path_var = tk.StringVar()
        ttk.Entry(file_frame, textvariable=self.production_path_var, width=80).grid(row=1, column=1, padx=5, pady=5)
        ttk.Button(file_frame, text="选择", command=lambda: self.select_file("production")).grid(row=1, column=2, padx=5)

        # 日期选择区域
        date_frame = ttk.LabelFrame(self.main_frame, text="日期选择", padding="10")
        date_frame.pack(fill=tk.X, padx=5, pady=5)

        # 年份选择
        ttk.Label(date_frame, text="年份:").grid(row=0, column=0, sticky="w")
        self.year_var = tk.StringVar(value=str(datetime.now().year))
        ttk.Entry(date_frame, textvariable=self.year_var, width=10).grid(row=0, column=1, sticky="w", padx=5)

        # 月份选择
        ttk.Label(date_frame, text="月份(1-12):").grid(row=0, column=2, sticky="w", padx=20)
        self.month_var = tk.StringVar(value=str(datetime.now().month))
        ttk.Entry(date_frame, textvariable=self.month_var, width=10).grid(row=0, column=3, sticky="w", padx=5)

        # 按钮区域
        button_frame = ttk.Frame(self.main_frame)
        button_frame.pack(pady=10)

        ttk.Button(button_frame, text="处理财务数据", command=self.process_finance_data).pack(side=tk.LEFT, padx=10)
        ttk.Button(button_frame, text="处理生产数据", command=self.process_production_data).pack(side=tk.LEFT, padx=10)
        ttk.Button(button_frame, text="全部处理", command=self.process_all_data).pack(side=tk.LEFT, padx=10)

        # 进度条框架
        progress_frame = ttk.LabelFrame(self.main_frame, text="处理进度", padding="10")
        progress_frame.pack(fill=tk.X, padx=5, pady=5)

        # 进度条
        self.progress_var = tk.DoubleVar()
        self.progress_bar = ttk.Progressbar(progress_frame, variable=self.progress_var, maximum=100)
        self.progress_bar.pack(fill=tk.X, padx=5, pady=5)

        # 日志区域
        log_frame = ttk.LabelFrame(self.main_frame, text="处理日志", padding="10")
        log_frame.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)

        # 创建日志文本框和滚动条
        self.status_text = tk.Text(log_frame, height=15, width=80, font=('微软雅黑', 9))
        scrollbar = ttk.Scrollbar(log_frame, orient="vertical", command=self.status_text.yview)
        self.status_text.configure(yscrollcommand=scrollbar.set)
        
        self.status_text.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

    def select_file(self, file_type):
        filename = filedialog.askopenfilename(
            title="选择文件",
            filetypes=[("Excel files", "*.xls;*.xlsx")]
        )
        if filename:
            if file_type == "production":
                self.production_path_var.set(filename)
                self.log_message(f"已选择生产月报文件: {filename}")

    def select_directory(self, dir_type):
        directory = filedialog.askdirectory(title="选择文件夹")
        if directory:
            if dir_type == "finance":
                self.finance_path_var.set(directory)
                self.log_message(f"已选择财务快报文件夹: {directory}")

    def log_message(self, message):
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        self.status_text.insert(tk.END, f"[{current_time}] {message}\n")
        self.status_text.see(tk.END)
        self.root.update()

    def process_finance_data(self):
        app = None
        try:
            if not self.finance_path_var.get():
                raise ValueError("请选择财务快报文件夹")

            self.log_message("开始处理财务数据...")
            self.progress_var.set(0)
            
            # 切换到财务快报文件夹
            os.chdir(self.finance_path_var.get())
            
            # 获取所有Excel文件
            files = [f for f in os.listdir() if f.endswith(('.xls', '.xlsx'))]
            total_files = len(files)
            
            if total_files == 0:
                raise ValueError("所选文件夹中没有Excel文件")
                
            # 处理数据
            dfs = []
            xm = ['营业收入','利润总额','20、售电单价','21、售电单价','36、发电供热标准煤单价','10、售电量']
            
            for i, file in enumerate(files):
                try:
                    self.log_message(f"正在处理文件: {file}")
                    
                    # 读取Excel文件,指定header=4
                    df = pd.read_excel(file, header=4)
                    
                    # 提取年月信息
                    year = file[0:4]
                    month = file[5:7]
                    
                    # 处理数据
                    df1 = df.loc[(df['行次']=='2') & (df['项目']=='   其中：（1）煤电')]
                    df = df.loc[df['项目'].isin(xm)]
                    df = df.loc[:, ['项目','年初数']]
                    df1 = df1.loc[:, ['项目','年初数']]
                    df['日期'] = f"{year}-{month}-01"
                    df1['日期'] = f"{year}-{month}-01"
                    
                    dfs.append(df)
                    dfs.append(df1)
                    
                    progress = (i + 1) / total_files * 100
                    self.progress_var.set(progress)
                    self.log_message(f"{year}年{month}月财务数据处理完成")
                    self.root.update()
                    
                except Exception as e:
                    self.log_message(f"处理文件 {file} 时出错: {str(e)}")
                    continue
            
            # 检查是否有数据可以合并
            if not dfs:
                raise ValueError("没有有效的数据可以处理")
                
            df = pd.concat(dfs)
            
            # 写入利润指标.xlsx
            target_file = os.path.join(os.path.dirname(self.finance_path_var.get()), '利润指标.xlsx')
            
            if not os.path.exists(target_file):
                raise ValueError(f"错误：利润指标.xlsx 文件不存在于路径：{os.path.dirname(target_file)}")
                
            app = xw.App(visible=False, add_book=False)
            workbook = app.books.open(target_file)
            worksheet = workbook.sheets["Sheet1"]
            worksheet.range('A2').value = df.values
            workbook.save()
            workbook.close()
            app.quit()
            app = None

            self.log_message("正在刷新利润指标.xlsx...")
            app = xw.App(visible=False, add_book=False)
            target_file = os.path.join(os.path.dirname(self.finance_path_var.get()), '利润指标.xlsx')
            workbook = app.books.open(target_file)
            
        # 刷新所有工作表
            for sheet in workbook.sheets:
                sheet.api.Calculate()
                
            workbook.save()
            workbook.close()
            app.quit()
            app = None
        
            self.log_message("利润指标.xlsx刷新完成")
            self.progress_var.set(100)
        
            self.progress_var.set(100)
            
        except Exception as e:
            messagebox.showerror("错误", str(e))
            self.log_message(f"错误: {str(e)}")
            if app:
                try:
                    app.quit()
                except:
                    pass

    def process_production_data(self):
        app = None
        app2 = None
        try:
            if not self.production_path_var.get():
                raise ValueError("请选择生产月报文件")
            if not self.month_var.get():
                raise ValueError("请输入月份")

            self.log_message("开始处理生产数据...")
            self.progress_var.set(0)

            # 获取程序所在目录
            current_dir = os.path.dirname(os.path.abspath(__file__))
            
            # 1. 处理生产指标数据
            self.log_message("正在处理生产指标...")
            target_file = os.path.join(current_dir, '生产指标分析.xlsx')
            
            if not os.path.exists(target_file):
                raise ValueError(f"错误：生产指标分析.xlsx 文件不存在于程序目录：{current_dir}")

            year_name = self.year_var.get()
            month_name = self.month_var.get()
            
            # 处理生产数据
            df = pd.DataFrame(columns=['序号','指标名称','计量单位','本月','本年本月止累计','日期'])
            
            for num in range(1, int(month_name)+1):
                name = str(num)+'月'
                try:
                    yb = pd.read_excel(self.production_path_var.get(), sheet_name=name, header=3)
                    yb1 = yb.loc[:,'序号.1':'本年本月止累计.1'].rename(columns={
                        '序号.1':'序号',
                        '指标名称.1':'指标名称',
                        '计量单位.1':'计量单位',
                        '本月.1':'本月',
                        '本年本月止累计.1':'本年本月止累计'
                    })
                    
                    yb = pd.concat([yb,yb1], ignore_index=True)
                    yb = yb.drop(labels=['序号.1','指标名称.1','计量单位.1','本月.1','本年本月止累计.1'], axis=1)
                    yb.reindex()
                    yb.drop(labels=[9,16,20,21,27,30,38,40,42,43], inplace=True)
                    yb['日期'] = year_name+'年'+name
                    df = pd.concat([yb,yb1], ignore_index=True)
                    
                    progress = (num / int(month_name)) * 40  # 生产指标处理占40%进度
                    self.progress_var.set(progress)
                    self.log_message(f"生产指标{name}处理完成")
                    self.root.update()
                    
                except Exception as e:
                    self.log_message(f"处理{name}时出错: {str(e)}")
                    continue

            del df['序号']
            del df['计量单位']
            del df['本年本月止累计']
            df = df[['日期','指标名称','本月']]
            ts = df[df['日期']==year_name+'年'+month_name+'月']

            # 写入生产指标分析.xlsx
            app = xw.App(visible=False, add_book=False)
            workbook = app.books.open(target_file)
            worksheet = workbook.sheets['基础数据']
            last_cell = worksheet['A1'].expand('table').last_cell
            last_row = last_cell.row
            worksheet.range(last_row+1, 1).value = ts.values
            workbook.save()
            workbook.close()
            app.quit()
            app = None
            
            self.progress_var.set(50)
            self.log_message("生产指标数据处理完成")

            # 2. 处理运行小时数据
            self.log_message("正在处理运行小时...")
            
            # 打开生产月报文件，读取负荷率sheet
            app = xw.App(visible=False, add_book=False)
            workbook = app.books.open(self.production_path_var.get())
            worksheet = workbook.sheets['负荷率']
            
            # 读取运行小时数据
            yxxs = worksheet.range('G2:K15').options(ndim=2).value
            df_yxxs = pd.DataFrame(yxxs, columns=['月份','1#','2#','合计','累计'])
            df_yxxs.drop(index=[0,1], inplace=True)
            
            # 处理数据
            df = df_yxxs.copy()
            month_name = self.month_var.get() + '月'
            df = df[df['月份']==month_name]
            
            workbook.close()
            app.quit()
            app = None
            
            self.progress_var.set(75)
            
            # 写入运行小时汇总.xls
            target_file = os.path.join(current_dir, '运行小时汇总.xls')
            if not os.path.exists(target_file):
                raise ValueError(f"错误：运行小时汇总.xls 文件不存在于程序目录：{current_dir}")
                
            app2 = xw.App(visible=False, add_book=False)
            workbook2 = app2.books.open(target_file)
            worksheet2 = workbook2.sheets[0]
            
            # 获取最后一行和列
            row = worksheet2.range('A1').expand('table').shape[0]
            column = worksheet2.range('A1').expand('table').shape[1]
            xdate = worksheet2.range(row, column).value
            
            # 写入新数据
            worksheet2.range(row+1, 1).value = df.values
            worksheet2.range(row+1, column).value = xdate + pd.DateOffset(months=1)
            
            workbook2.save()
            workbook2.close()
            app2.quit()
            app2 = None

            self.log_message("运行小时数据处理完成")
            self.progress_var.set(100)
            self.log_message("所有生产数据处理完成")
            
        except Exception as e:
            messagebox.showerror("错误", str(e))
            self.log_message(f"错误: {str(e)}")
        finally:
            if app:
                try:
                    app.quit()
                except:
                    pass
            if app2:
                try:
                    app2.quit()
                except:
                    pass

    def process_all_data(self):
        try:
            self.process_finance_data()
            self.process_production_data()
            
            # 获取程序所在目录
            current_dir = os.path.dirname(os.path.abspath(__file__))
            
            self.log_message("正在刷新所有数据表...")
            
            app = xw.App(visible=False, add_book=False)
            
            # 刷新利润指标.xlsx
            try:
                profit_file = os.path.join(os.path.dirname(self.finance_path_var.get()), '利润指标.xlsx')
                if os.path.exists(profit_file):
                    wb1 = app.books.open(profit_file)
                    wb1.sheets[0].range('A1').expand('table').calculate()
                    wb1.save()
                    wb1.close()
                    self.log_message("利润指标.xlsx 刷新完成")
            except Exception as e:
                self.log_message(f"刷新利润指标.xlsx时出错: {str(e)}")

            # 刷新生产指标分析.xlsx
            try:
                production_file = os.path.join(current_dir, '生产指标分析.xlsx')
                if os.path.exists(production_file):
                    wb2 = app.books.open(production_file)
                    wb2.sheets['基础数据'].range('A1').expand('table').calculate()
                    wb2.save()
                    wb2.close()
                    self.log_message("生产指标分析.xlsx 刷新完成")
            except Exception as e:
                self.log_message(f"刷新生产指标分析.xlsx时出错: {str(e)}")

            # 刷新运行小时汇总.xls
            try:
                hours_file = os.path.join(current_dir, '运行小时汇总.xls')
                if os.path.exists(hours_file):
                    wb3 = app.books.open(hours_file)
                    wb3.sheets[0].range('A1').expand('table').calculate()
                    wb3.save()
                    wb3.close()
                    self.log_message("运行小时汇总.xls 刷新完成")
            except Exception as e:
                self.log_message(f"刷新运行小时汇总.xls时出错: {str(e)}")

            app.quit()
            self.log_message("所有数据表刷新完成")
            
        except Exception as e:
            messagebox.showerror("错误", str(e))
            self.log_message(f"错误: {str(e)}")
            if 'app' in locals():
                try:
                    app.quit()
                except:
                    pass

def main():
    root = tk.Tk()
    app = DataProcessingApp(root)
    root.mainloop()

if __name__ == "__main__":
    main()
